﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Data;

namespace Marbles.Data
{
    public class PostgreSQLDataSourceProvider : BaseDataSource, IDataSource
    {

        private NpgsqlConnection _dbc = null;

        public PostgreSQLDataSourceProvider(string ConnectionString)
        {
            this._dbc = new NpgsqlConnection(ConnectionString);
        }

        public void Close()
        {
            if (this._dbc.State != ConnectionState.Closed)
            {
                this._dbc.Close();
            }
        }

        private NpgsqlCommand ConvertToSQLCommand(DataSourceCommand command)
        {
            string query = command.CommandText;
            NpgsqlCommand cmd = new NpgsqlCommand(query, this._dbc);
            foreach (string s in command.Parameters.Keys)
            {
                if (command.Parameters[s].GetType() == typeof(byte[]))
                {
                    NpgsqlParameter parm = new NpgsqlParameter(s, SqlDbType.Image);
                    parm.Value = command.Parameters[s];
                    cmd.Parameters.Add(parm);
                }
                else
                {
                    if (command.Parameters[s] == null)
                    {
                        cmd.Parameters.Add(new NpgsqlParameter(s, DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new NpgsqlParameter(s, command.Parameters[s]));
                    }
                }
            }
            return cmd;
        }

        public int ExecuteCommand(DataSourceCommand Command)
        {
            NpgsqlCommand cmd = this.ConvertToSQLCommand(Command);
            if (!this.IsConnected())
            {
                this.Open();
            }
            int value = cmd.ExecuteNonQuery();
            this.Close();
            return value;
        }

        public DataSet FetchDataSet(DataSourceCommand Command)
        {
            DataSet ds = new DataSet();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(this.ConvertToSQLCommand(Command));
            if (!this.IsConnected())
            {
                this.Open();
            }
            da.Fill(ds);
            this.Close();
            return ds;
        }

        public DataTable FetchDataTable(DataSourceCommand Command)
        {
            DataTable dt = new DataTable();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(this.ConvertToSQLCommand(Command));
            if (!this.IsConnected())
            {
                this.Open();
            }
            da.Fill(dt);
            this.Close();
            return dt;
        }

        public IDataReader FetchReader(DataSourceCommand Command)
        {
            return this.ConvertToSQLCommand(Command).ExecuteReader();
        }

        public bool IsConnected()
        {
            return (this._dbc.State == ConnectionState.Open);
        }

        public void Open()
        {
            if ((this._dbc.State != ConnectionState.Connecting) & (this._dbc.State != ConnectionState.Open))
            {
                this._dbc.Open();
            }
        }

        // Properties
        public string Provider
        {
            get
            {
                return "Npgsql";
            }
        }

        internal int MajorVersion
        {
            get
            {
                int value = 9;
                _dbc.Open();
                string[] args = _dbc.ServerVersion.Split('.');
                _dbc.Close();
                if (args.Length >= 1)
                {
                    if (!int.TryParse(args[0], out value)) { value = 1; }
                }
                return value;
            }
        }

        public string GetMetaData(string database, string owner, string table, string KeyName)
        {
            //Not Supported
            return string.Empty;
        }

        public string GetMetaData(string database, string owner, string table, string column, string KeyName)
        {
            //Not Supported
            return string.Empty;
        }

        public string[] ListDatabases()
        {
            List<string> db = new List<string>();
            
            return db.ToArray();
        }

        public DataSourceTable[] ListTables(string Database)
        {
            List<DataSourceTable> tbllist = new List<DataSourceTable>();
            
            return tbllist.ToArray();
        }

        public DataSourceTableDefinition GetTableDefinition(DataSourceTable Table)
        {
            DataSourceTableDefinition tbl = new DataSourceTableDefinition(Table);
            
            return tbl;
        }

        private SqlDbType GetColumnType(string value)
        {

            switch (value.ToLower())
            {
                case "tinyint": return SqlDbType.TinyInt;
                case "smallint": return SqlDbType.SmallInt;
                case "mediumint": return SqlDbType.Int;
                case "int": return SqlDbType.Int;
                case "bigint": return SqlDbType.BigInt;
                case "datetime": return SqlDbType.DateTime;
                case "date": return SqlDbType.Date;
                case "timestamp": return SqlDbType.Timestamp;
                case "time": return SqlDbType.Time;
                case "year": return SqlDbType.DateTime2;
                case "char": return SqlDbType.Char;
                case "varchar": return SqlDbType.VarChar;
                case "binary": return SqlDbType.Binary;
                case "varbinary": return SqlDbType.VarBinary;
                case "blob": return SqlDbType.Image;
                case "text": return SqlDbType.Text;
                case "enum": return SqlDbType.Udt;
                case "set": return SqlDbType.Udt;
            }

            return SqlDbType.Variant;
        }

        private int GetIntValue(object value)
        {
            if (value == null) { return 0; }
            if (value == DBNull.Value) { return 0; }
            return Convert.ToInt32(value);
        }

        private string GetDefault(object value)
        {
            if (value == null) { return string.Empty; }
            if (value == DBNull.Value) { return string.Empty; }
            return value.ToString();
        }

        private bool GetBoolValue(string value)
        {
            if (string.IsNullOrEmpty(value)) { return false; }

            if (value.ToUpper() == "NO") { return false; }
            return true;
        }
    }
}
